package pro4;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDAO {
    private Connection connection;

    public ProductDAO() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/cwl_db", "root", "123456");
        } catch (Exception e) {
            throw new SQLException("数据库连接失败", e);
        }
    }

    // 添加商品
    public void addProduct(String id, String name, double price, int stock, String imageUrl) throws SQLException {
        String sql = "INSERT INTO products (id, name, price, stock, image_url) VALUES (?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, id);
            stmt.setString(2, name);
            stmt.setDouble(3, price);
            stmt.setInt(4, stock);
            stmt.setString(5, imageUrl);
            stmt.executeUpdate();
        }
    }

    // 获取所有商品
    public List<Product> getAllProducts() throws SQLException {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT * FROM products";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                products.add(new Product(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getDouble("price"),
                        rs.getInt("stock"),
                        rs.getString("image_url")
                ));
            }
        }
        return products;
    }

    // 删除商品
    public void deleteProduct(String id) throws SQLException {
        String sql = "DELETE FROM products WHERE id=?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, id);
            stmt.executeUpdate();
        }
    }

    // 更新商品（按字段）
    public void updateProduct(String id, String name, double price, int stock, String imageUrl) throws SQLException {
        String sql = "UPDATE products SET name=?, price=?, stock=?, image_url=? WHERE id=?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, name);
            stmt.setDouble(2, price);
            stmt.setInt(3, stock);
            stmt.setString(4, imageUrl);
            stmt.setString(5, id);
            stmt.executeUpdate();
        }
    }

    // 获取单个商品
    public Product getProductById(String id) throws SQLException {
        String sql = "SELECT * FROM products WHERE id=?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return new Product(
                            rs.getString("id"),
                            rs.getString("name"),
                            rs.getDouble("price"),
                            rs.getInt("stock"),
                            rs.getString("image_url")
                    );
                }
            }
        }
        return null;
    }

    // 更新商品（通过对象）
    public void updateProduct(Product product) throws SQLException {
        String sql = "UPDATE products SET name=?, price=?, stock=?, image_url=? WHERE id=?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, product.getName());
            stmt.setDouble(2, product.getPrice());
            stmt.setInt(3, product.getStock());
            stmt.setString(4, product.getImageUrl());
            stmt.setString(5, product.getId());
            stmt.executeUpdate();
        }
    }

    // 关闭连接
    public void close() throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}
